package mango.web.server.admin;

import java.io.FileReader;
import java.io.IOException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import mango.web.server.ViewS;

import org.supercsv.io.CsvListReader;
import org.supercsv.prefs.CsvPreference;

public class ImportViewS extends ViewS {
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		resp.getWriter().write(this.doImport2());
		super.doGet(req, resp);
	}

	public static ImportViewS get(String key) {
		if ("".equals(key)) { return new ImportViewS(); }
		return null;
	}
	
	public void doExport(){
		try {
			
			String str = (String)this.getThreadLocalRequest().getSession().getAttribute("export");
			byte[] b = str.getBytes(Charset.forName("UTF-8"));
			int length = 0;

			HttpServletResponse resp = getThreadLocalResponse();
			ServletOutputStream op = resp.getOutputStream();
			ServletContext context = getServletConfig().getServletContext();
			resp.setContentType("application/octet-stream");
			resp.setContentLength((int) b.length);
			resp.setHeader("Content-Disposition", "attachment; filename*=\"utf-8''" + "MailingList.csv" + "");

			op.write(b);
			
			op.flush();
			op.close();

		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	public String doImport() {
		Connection con = null;
		PreparedStatement stm = null;
		try {
			con = this.getDs().getConnection();

			String sql = "REPLACE INTO Tempature2 SET regionId=?, dateT=?, maxT=?, minT=?";
			stm = con.prepareStatement(sql);
			for (int i = 1; i <= 9; i++) {
				if (i == 3 || i == 6) continue;
				CsvListReader csvListReader = new CsvListReader(new FileReader(this.getFilePath("data/T_" + i + ".csv")), CsvPreference.EXCEL_PREFERENCE);
				List<String> list = null;
				int j = 0;
				while ((list = csvListReader.read()) != null) {
					if (j == 0) {
						j++;
						continue;
					}
					String arr[] = list.get(4).split("-");

					stm.setInt(1, i);
					stm.setDate(2, new Date(1900 - 1900, Integer.parseInt(arr[1]) - 1, Integer.parseInt(arr[2])));
					stm.setDouble(3, Double.parseDouble(list.get(0)));
					stm.setDouble(4, Double.parseDouble(list.get(1)));
					stm.executeUpdate();
					j++;
				}
			}
			this.close(stm);

			/*
			String sql = "REPLACE INTO Tempature SET regionId=?, dateT=?, maxT=?, minT=?";
			stm = con.prepareStatement(sql);
			for (int i = 1; i <= 8; i++) {
				if (i == 3 || i == 6) continue;
				CsvListReader csvListReader = new CsvListReader(new FileReader(this.getFilePath("data/Temp_" + i + ".csv")), CsvPreference.EXCEL_PREFERENCE);
				List<String> list = null;
				int j = 0;
				while ((list = csvListReader.read()) != null) {
					if (j == 0) {
						j++;
						continue;
					}
					String arr[] = list.get(0).split("-");

					stm.setInt(1, i);
					stm.setDate(2, new Date(Integer.parseInt(arr[0]) - 1900 + 1, Integer.parseInt(arr[1]) - 1, Integer.parseInt(arr[2])));
					stm.setDouble(3, Double.parseDouble(list.get(1)));
					stm.setDouble(4, Double.parseDouble(list.get(2)));
					stm.executeUpdate();
					j++;
				}
			}
			this.close(stm);
			*/

			/*
			String sql = "REPLACE INTO LookupT SET regionId=?, varietyId=?, heat=?";
			stm = con.prepareStatement(sql);

			CsvListReader csvListReader = new CsvListReader(new FileReader(this.getFilePath("data/Temp_Lookup.csv")), CsvPreference.EXCEL_PREFERENCE);
			List<String> list = null;
			int j = 0;
			while ((list = csvListReader.read()) != null) {
				int r1 = j / 8;
				int r2 = j % 8;
				if (r2 == 0) {
					j++;
					continue;
				}

				int regionId = 0;
				if (r1 == 0) regionId = 0;
				if (r1 == 1) regionId = 1;
				if (r1 == 2) regionId = 2;
				if (r1 == 3) regionId = 4;
				if (r1 == 4) regionId = 5;
				if (r1 == 5) regionId = 7;
				if (r1 == 6) regionId = 8;

				int varietyId = 0;
				if (r2 == 1) varietyId = 21;
				if (r2 == 2) varietyId = 4;
				if (r2 == 3) varietyId = 3;
				if (r2 == 4) varietyId = 11;
				if (r2 == 5) varietyId = 12;
				if (r2 == 6) varietyId = 18;
				if (r2 == 7) varietyId = 1;

				stm.setInt(1, regionId);
				stm.setInt(2, varietyId);
				stm.setInt(3, Integer.parseInt(list.get(0)));
				stm.executeUpdate();
				j++;
			}
			*/
			this.close(stm);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.close(stm);
			this.close(con);
		}

		return "done";
	}

	public String doImport2() {
		Connection con = null;
		PreparedStatement stm = null;
		String rootPath = this.getFilePath("");
		try {
			String sql = "REPLACE INTO Member SET memberId=? , businessName=?, abn=?, businessType=?, other=?, membershipType=?, financialMember=?, annualReport=?, authorisedContact=?, marketingGroupUsed=?, packsOwnProduce=?, marketGroup=?, brand=?";
			con = this.getDs().getConnection();
			stm = con.prepareStatement(sql);
			CsvListReader csvListReader = new CsvListReader(new FileReader(rootPath + "data/Member.csv"), CsvPreference.EXCEL_PREFERENCE);
			List<String> list = null;
			while ((list = csvListReader.read()) != null) {
				stm.setInt(1, Integer.parseInt(list.get(0).replaceAll(",", "")));
				stm.setString(2, list.get(1));
				stm.setString(3, list.get(2));
				String str = list.get(3).replaceAll("", "&&");
				if (!"".equals(str) && !str.endsWith("&&")) str = str + "&&";
				stm.setString(4, str);
				stm.setString(5, list.get(4));
				stm.setString(6, list.get(5));
				stm.setString(7, list.get(6));
				stm.setString(8, list.get(7));
				//stm.setString(9, list.get(8));
				if (!"".equals(list.get(8))) stm.setInt(9, Integer.parseInt(list.get(8)));
				else stm.setNull(9, Types.INTEGER);
				stm.setString(10, list.get(9));
				stm.setString(11, list.get(10));
				stm.setString(12, list.get(11));
				stm.setString(13, list.get(12));
				stm.executeUpdate();
			}
			this.close(stm);

			sql = "REPLACE INTO Contact SET contactId=?, memberId=?, firstname=?, surname=?, BHPhone=?, AHPhone=?, fax=?, identifier=?, mobile=?, email=?, service=?, businessStreet=?, businessCity=?, businessState=?, businessCountry=?, businessPostCode=?, postalStreet=?, postalCity=?, postalState=?, postalCountry=?, postalPostCode=?, mailMemberId=?, mailContactId=?, emailFaxContactId=?";
			stm = con.prepareStatement(sql);
			csvListReader = new CsvListReader(new FileReader(rootPath + "data/Contact.csv"), CsvPreference.EXCEL_PREFERENCE);
			list = null;
			while ((list = csvListReader.read()) != null) {
				if("".equals(list.get(0))) continue;
				stm.setInt(1, Integer.parseInt(list.get(0)));
				stm.setInt(2, Integer.parseInt(list.get(1)));
				stm.setString(3, list.get(2));
				stm.setString(4, list.get(3));
				stm.setString(5, list.get(4));
				stm.setString(6, list.get(5));
				//\\stm.setString(7, list.get(6));
				stm.setString(7, list.get(6).replaceAll("--", ""));
				//\\
				stm.setString(8, list.get(7));
				stm.setString(9, list.get(8));
				stm.setString(10, list.get(9));
				String str = list.get(10).replaceAll("", "&&");
				if (!"".equals(str) && !str.endsWith("&&")) str = str + "&&";
				//\\
				str.replaceAll("AMIA Activities FAX&&", "");
				str.replaceAll("AMIA Activities EMAIL&&", "");
				if(!"".equals(list.get(9).trim())){
					str += "AMIA Activities EMAIL&&";
				} else {
					if(!"".equals(list.get(6).replaceAll("--", "").trim())){
						str += "AMIA Activities FAX&&";
					}
				}
				//\\
				stm.setString(11, str);
				stm.setString(12, list.get(11));
				stm.setString(13, list.get(12));
				stm.setString(14, list.get(13));
				stm.setString(15, list.get(14));
				stm.setString(16, list.get(15));
				stm.setString(17, list.get(16));
				stm.setString(18, list.get(17));
				stm.setString(19, list.get(18));
				stm.setString(20, list.get(19));
				stm.setString(21, list.get(20));

				if (!"".equals(list.get(21))) stm.setInt(22, Integer.parseInt(list.get(21)));
				else stm.setNull(22, Types.INTEGER);

				if (!"".equals(list.get(22))) stm.setInt(23, Integer.parseInt(list.get(22)));
				else stm.setNull(23, Types.INTEGER);

				if (!"".equals(list.get(23))) stm.setInt(24, Integer.parseInt(list.get(23)));
				else stm.setNull(24, Types.INTEGER);

				stm.executeUpdate();
			}
			this.close(stm);

			sql = "REPLACE INTO Variety SET varietyId=?, varietyName=?";
			stm = con.prepareStatement(sql);
			//HashMap<String, String> varietyMap = new HashMap<String, String>();
			csvListReader = new CsvListReader(new FileReader(rootPath + "data/Variety.csv"), CsvPreference.EXCEL_PREFERENCE);
			list = null;
			while ((list = csvListReader.read()) != null) {
				if ("".equals(list.get(1))) continue;
				//varietyMap.put(list.get(0), list.get(1));
				stm.setObject(1, list.get(0));
				stm.setString(2, list.get(1));
				stm.executeUpdate();
			}
			this.close(stm);

			sql = "REPLACE INTO Orchard SET orchardId=?, memberId=?, physicalLocation=?, postCode=?, state=?";
			stm = con.prepareStatement(sql);
			csvListReader = new CsvListReader(new FileReader(rootPath + "data/Orchard.csv"), CsvPreference.EXCEL_PREFERENCE);
			list = null;
			while ((list = csvListReader.read()) != null) {
				if ("".equals(list.get(0))) continue;
				stm.setInt(1, Integer.parseInt(list.get(0)));
				stm.setInt(2, Integer.parseInt(list.get(1)));
				stm.setString(3, list.get(2).replaceAll("", "\n"));
				stm.setString(4, list.get(3));
				stm.setString(5, list.get(4));
				stm.executeUpdate();
			}
			this.close(stm);

			stm = con.prepareStatement("DELETE FROM Tree");
			stm.executeUpdate();
			this.close(stm);

			sql = "REPLACE INTO Tree SET orchardId=?, yearPlanting=?, varietyId=?, DTR=?, BTR=?, area=?, areaType=?, tree=?";
			stm = con.prepareStatement(sql);
			csvListReader = new CsvListReader(new FileReader(rootPath + "data/Tree.csv"), CsvPreference.EXCEL_PREFERENCE);
			list = null;
			while ((list = csvListReader.read()) != null) {
				if ("".equals(list.get(0))) continue;
				stm.setInt(1, Integer.parseInt(list.get(0)));
				stm.setString(2, list.get(1));
				if ("".equals(list.get(2))) list.set(2, null);
				stm.setObject(3, list.get(2));
				stm.setString(4, list.get(3));
				stm.setString(5, list.get(4));
				stm.setString(6, list.get(5));
				stm.setString(7, list.get(6));
				if ("".equals(list.get(7))) list.set(7, "0");
				stm.setInt(8, Integer.parseInt(list.get(7)));
				stm.executeUpdate();
			}
			this.close(stm);

			sql = "SELECT orchard.orchardId, contact.contactId FROM orchard, contact where orchard.memberId = contact.memberId";
			stm = con.prepareStatement(sql);
			ResultSet rs = stm.executeQuery();
			HashMap<String, String> map = new HashMap<String, String>();
			while (rs.next()) {
				String orchardId = rs.getString("orchardId");
				String str = map.get(orchardId);
				if (str == null) str = "";
				str += "&" + rs.getString("contactId") + "&";
				map.put(orchardId, str);
			}
			this.close(stm);

			for (String orchardId : map.keySet()) {
				String emailContactId = map.get(orchardId);
				sql = "UPDATE orchard SET emailContactId='" + emailContactId + "' WHERE orchardId=" + orchardId;
				stm = con.prepareStatement(sql);
				stm.executeUpdate();
				this.close(stm);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.close(stm);
			this.close(con);
		}

		return "done";
	}
}
